package com.webaccess.retailws.db;

/**
 *
 * @author Windows
 */
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBConnection {
    //String DBClass= "org.gjt.mm.mysql.Driver";

    String DBClass = "com.mysql.jdbc.Driver";
    String dbURL = "jdbc:mysql://localhost:3306/retailws";
    String userName = "root";
    String password = "root";
    PreparedStatement pstmt = null;
    Statement stmt = null;

    public DBConnection() {
    }

    public Connection getConnection() throws Exception {
        Connection conn = null;
        try {
            Class.forName(DBClass).newInstance();
            conn = DriverManager.getConnection(dbURL, userName, password);
        } catch (SQLException e) {
            System.out.println("Exception " + e);
        } finally {
            return conn;
        }
    }

    public void freeConnection(Connection conn) throws SQLException {
        if (conn != null) {
            try {
                conn.close();
//                System.out.println(" Connection freed no of open connections .."+openConns );
            } catch (SQLException e) {
                System.out.println("Exception " + e);
            }
        } else {
            return;
        }
    }

    public void freeResultSet(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException ex) {
                System.out.println("Exception " + ex);
//                Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
            }
        } else {
            return;
        }
    }

    /**
     * Execute a SELECT statement
     */
    public ResultSet executeQuery(Connection conn, String sql) throws SQLException {

        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
        } catch (SQLException e) {
            throw new SQLException(sql + "\n" + e);
        } finally {
            return rs;
        }
    }

    /**
     * Execute an INSERT, UPDATE, or DELETE.  The calling routine must close
     * the result set.
     */
    public int executeUpdate(Connection conn, String sql) throws SQLException {
        //debug(sql, 1);

        int result = 0;
        try {
            pstmt = conn.prepareStatement(sql);
            result = pstmt.executeUpdate();

        } catch (SQLException e) {
            throw new SQLException(sql + "\n" + e);
        } finally {
            return result;
        }
    }

    public int[] executebatchUpdate(String[] queries) throws SQLException {
        // debug(queries, 1);
        Connection con = null;
        int results[] = null;
        try {
            con = getConnection();
            con.setAutoCommit(false);

            stmt = con.createStatement();
            int strLength = queries.length;
            for (int i = 0; i < strLength; i++) {
                stmt.addBatch(queries[i]);
            }
            results = stmt.executeBatch();
            con.commit();

        } catch (Exception e) {
            //throw new SQLException(queries + "\n" + e);
        } finally {
            con.setAutoCommit(false);
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
                stmt = null;
            }
            freeConnection(con);
            con = null;
            //  conn.commit();
            return results;
        }
    }

    /**
     * Execute a PROCEDURE
     */
    public ResultSet executeProcedure(Connection conn, String sql) throws SQLException {

        ResultSet rs = null;
        try {
            pstmt = conn.prepareCall(sql);
            rs = pstmt.executeQuery();
        } catch (SQLException e) {
            throw new SQLException(sql + "\n" + e);
        } finally {
            return rs;
        }
    }
}

/*
 * if (pstmt != null) {
try {
pstmt.close();
} catch (Exception ex) {
ex.printStackTrace();
}
pstmt = null;
}
 *
 */
